package dao;

import java.sql.Connection;
import java.sql.DriverManager;
import java.sql.ResultSet;
import java.sql.SQLException;
import java.sql.Statement;
import java.util.Vector;

import metier.Adherent;

public class DaoAdherent
{
	//methode static getLesAdherents
	public static Vector <Adherent> getLesAdherents()
	{	
		String nomDriver = "org.gjt.mm.mysql.Driver";
		
		final String url ="jdbc:mysql://localhost/bdd_centre";
		final String user="root";
		final String password="";
		
		Statement stLienBd;
		Connection connect;
		
		Vector <Adherent> lesAdherents;
		lesAdherents = new Vector<Adherent>();
		
		connect = null;
	
		try
		{
			// 1 - Chargement du driver

			Class.forName(nomDriver);
			
			// 2 - Etablissement de la connection
			connect = DriverManager.getConnection(url, user, password);
			
			//------------------------
			stLienBd = connect.createStatement();
		
			String req = "select * from adherent";
			ResultSet resultat = stLienBd.executeQuery(req);
		
			while(resultat.next())
			{
				Adherent unAdherent = new Adherent (resultat.getInt("numAdherent"),resultat.getString("nom"), resultat.getString("prenom"), resultat.getString("login"), resultat.getString("password"), resultat.getString("type"));
				lesAdherents.add(unAdherent);
			}
			resultat.close();
		//---------------------
		}
		catch(ClassNotFoundException e)
		{
			System.out.println("problème chargement de driver " + e.getMessage());
		}
		
		catch(SQLException e)
		{
			System.out.println("problème lors de la connexion à la bdd " + e.getMessage());
		}
		
		return lesAdherents;
	}

	//ajoute un adherent au vector lesAdherents
	public static int ajouterAdherent(Adherent unAdherent) throws SQLException
	{
			Statement stLienBd;
			Connection connect;
			connect = null;
			int nb;
			connect = MySqlConnection.getConnection("bdd_centre", "root", "");
			stLienBd = connect.createStatement();
			
			String req = "Insert into adherent (nom, prenom, login, password, type) " +
					"values('" + unAdherent.getNom() + "' , '" + unAdherent.getPrenom() + "' , '" +unAdherent.getLogin() + "'," +
							"'"  +unAdherent.getPassword() + "' ,'"  +unAdherent.getType() + "')";
			
			System.out.println(req);
			nb = stLienBd.executeUpdate(req);
			
			return nb;
    }	
	
	//modifie l'adherent dans le vector lesAdherents
	public static int modifierAdherent(Adherent unAdherent) throws SQLException
	{
		@SuppressWarnings("unused")
		Connection connect;
		connect = null;
		int nb;
		connect = MySqlConnection.getConnection("bdd_centre", "root", "");
				
        String req = "Update Adherent set nom ='" + unAdherent.getNom() + "', prenom = '" + unAdherent.getPrenom() + "' ,  login = '"
        + unAdherent.getLogin() + "' , password = '" +unAdherent.getPassword() + "' where numAdherent ='" +unAdherent.getNumAdherent()+ "' ";
        
        nb = MySqlFramework.executerMaj(req);
        
        return nb;
        
    }
	
	//suppression de l'adherent à partir de son numero d'adherent
	public static int supprimerAdherent(int leNumAdherent) throws SQLException
	{
		@SuppressWarnings("unused")
		Statement stLienBd;
		Connection connect;
		connect = null;
		int nb;
		
		connect = MySqlConnection.getConnection("bdd_centre", "root", "");
		stLienBd = connect.createStatement();
		
		String reqDelete = "DELETE FROM Adherent WHERE NumAdherent = '" + leNumAdherent + "' ";
		nb = MySqlFramework.executerMaj(reqDelete);
		
		return nb;
	}
	
}
